﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Collections;

namespace Mengoo.DB
{
    public static class SqlAdapt
    {
        #region Insert
        /// <summary>
        /// 匹配Insert
        /// </summary>
        /// <param name="model">实体model</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static string AdaptInsertSql(object model,string tableName)
        {
            return AdaptInsertSql(model, tableName);
        }

        /// <summary>
        /// 匹配Insert
        /// </summary>
        /// <param name="model">实体model</param>
        /// <param name="tableName">表名</param>
        /// <param name="exceptColumn">不需要插入的列名，用,进行分隔</param>
        /// <returns></returns>
        public static string AdaptInsertSql(object model, string tableName, string exceptColumn)
        {
            string[] ec = exceptColumn.Split(',');
            List<string> columns = new List<string>();
            List<string> values = new List<string>();

            Type type = model.GetType();
            foreach (PropertyInfo pi in type.GetProperties())
            {
                if (!ec.Contains(pi.Name))
                {
                    object value = pi.GetValue(model, null);
                    if (value != null)
                    {
                        columns.Add(pi.Name);
                        values.Add(AdaptSqlValue(value));
                    }
                }
            }
            return AdaptInsertSql(tableName, String.Join(",", columns.ToArray()), String.Join(",", values.ToArray()));
        }

        /// <summary>
        /// 匹配Insert，插入多条记录
        /// </summary>
        /// <param name="modelList">记录实体集合</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static List<string> AdaptInsertSql(List<object> modelList, string tableName)
        {
            List<string> sqlList = new List<string>();
            foreach (object model in modelList)
            {
                sqlList.Add(AdaptInsertSql(model, tableName));
            }
            return sqlList;
        }
        /// <summary>
        /// 匹配Insert，插入多条记录
        /// </summary>
        /// <param name="modelList">记录实体集合</param>
        /// <param name="tableName">表名</param>
        /// <param name="exceptColumn">不需要插入的列名，用,进行分隔</param>
        /// <returns></returns>
        public static List<string> AdaptInsertSql(List<object> modelList, string tableName, string exceptColumn)
        {
            List<string> sqlList = new List<string>();
            foreach (object model in modelList)
            {
                sqlList.Add(AdaptInsertSql(model, tableName, exceptColumn));
            }
            return sqlList;
        }

        /// <summary>
        /// 匹配Insert
        /// </summary>
        /// <param name="ht">要插入的的键值对</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static string AdaptInsertSql(Hashtable columns, string tableName)
        {
            string[] cs = new string[columns.Count];
            string[] vs = new string[columns.Count];
            int i=0;
            foreach (string key in columns.Keys)
            {
                cs[i] = key;
                vs[i] = AdaptSqlValue(columns[key]);
                i++;
            }

            return AdaptInsertSql(tableName, String.Join(",", cs), String.Join(",", vs));
        }

        /// <summary>
        /// 匹配Insert
        /// </summary>
        /// <param name="htList">要插入的的键值对的集合</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static List<string> AdaptInsertSql(List<Hashtable> columnsList, string tableName)
        {
            List<string> sqlList = new List<string>();
            foreach (Hashtable columns in columnsList)
            {
                sqlList.Add(AdaptInsertSql(columns, tableName));
            }
            return sqlList;
        }

        /// <summary>
        /// 匹配Insert
        /// </summary>
        /// <param name="tableName">要插入的表名</param>
        /// <param name="columns">要插入的列名，用,分隔</param>
        /// <param name="values">列名对应的值，用,分隔，请注意字符串等类型字段的''</param>
        /// <returns></returns>
        public static string AdaptInsertSql(string tableName, string columns, string values)
        {
            return string.Format("insert into {0} ({1}) values ({2})", tableName, columns, values);
        }
        #endregion

        #region Update
        /// <summary>
        /// 匹配Update
        /// </summary>
        /// <param name="model">实体model</param>
        /// <param name="tableName">表名</param>
        /// <param name="exceptColumn">不需要更新的列名，用,进行分隔</param>
        /// <returns></returns>
        public static string AdaptUpdateSql(object model, string tableName, string where)
        {
            return AdaptUpdateSql(model, tableName, "", where);
        }
        
        /// <summary>
        /// 匹配Update
        /// </summary>
        /// <param name="model">实体model</param>
        /// <param name="tableName">表名</param>
        /// <param name="exceptColumn">不需要插入的列名，用,进行分隔</param>
        /// <param name="where">更新条件</param>
        /// <returns></returns>
        public static string AdaptUpdateSql(object model, string tableName, string exceptColumn, string where)
        {
            string[] ec = exceptColumn.Split(',');
            Type type = model.GetType();
            List<string> columns = new List<string>();

            foreach (PropertyInfo pi in type.GetProperties())
            {
                if (!ec.Contains(pi.Name))
                {
                    object value=pi.GetValue(model,null);
                    if (value != null)
                    {
                        columns.Add(string.Format("{0}={1}", pi.Name, AdaptSqlValue(value)));
                    }
                }
            }
            return AdaptUpdateSql(tableName, String.Join(",", columns.ToArray()), where);
        }

        /// <summary>
        /// 匹配Update
        /// </summary>
        /// <param name="columns">已经匹配的Hashtable键值对</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">更新条件</param>
        /// <returns></returns>
        public static string AdaptUpdateSql(Hashtable columns, string tableName, string where)
        {
            string[] cs = new string[columns.Count];
            int i = 0;
            foreach (string key in columns.Keys)
            {
                cs[i] = string.Format("{0}={1}", key, AdaptSqlValue(columns[key]));
                i++;
            }
            return AdaptUpdateSql(String.Join(",",cs), tableName, where);
        }

        /// <summary>
        /// 匹配Update
        /// </summary>
        /// <param name="columns">列名</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">更新条件</param>
        /// <returns></returns>
        public static string AdaptUpdateSql(string columns, string tableName, string where)
        {
            return string.Format("update {0} set {1} {2}", tableName, columns, AdaptWhere(where));
        }
        #endregion

        #region Del
        /// <summary>
        /// 匹配Del
        /// </summary>
        /// <summary>
        /// 匹配Update
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">实体model</param>
        /// <param name="where">删除条件</param>
        /// <returns></returns>
        public static string AdaptDelSql(string tableName, string where)
        {
            return string.Format("delete from {0} {1}", tableName, AdaptWhere(where));
        }
        #endregion

        #region Select
        /// <summary>
        /// 匹配Select
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要查询的列名</param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static string AdaptSelectSql(string tableName, string columns, string where)
        {
            if (string.IsNullOrEmpty(columns))
            {
                columns = "*";
            }
            return string.Format("select {0} from {1} {2}", columns, tableName, AdaptWhere(where));
        }
        #endregion

        #region Where
        /// <summary>
        /// 匹配Where
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string AdaptWhere(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                return "";
            }
            return string.Format("where {0}", where);
        }

        /// <summary>
        /// 匹配Key where
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="o">对象实体</param>
        /// <returns></returns>
        public static string AdaptKeyWhere(string keyField, object o)
        {
            return string.Format(" {0}={1}", keyField, AdaptSqlValue(o.GetType().GetField(keyField).GetValue(o)));
        }

        /// <summary>
        /// 配置相等的SQL
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptEquals(string field, object value)
        {
            return string.Format(" {0}={1}", field, AdaptSqlValue(value));
        }

        /// <summary>
        /// 匹配like
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptLike(string field, string value)
        {
            return string.Format(" {0} like '%{1}%'", field, value);
        }

        /// <summary>
        /// 匹配左like
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptLeftLike(string field, string value)
        {
            return string.Format(" {0} like '%{1}'", field, value);
        }

        /// <summary>
        /// 匹配右like
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptRightLike(string field, string value)
        {
            return string.Format(" {0} like '{1}%'", field, value);
        }

        /// <summary>
        /// 匹配大于
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptGreaterThan(string field, object value)
        {
            return string.Format(" {0}>{1}", field, AdaptSqlValue(value));
        }

        /// <summary>
        /// 匹配大于等于
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptGreaterThanOrEqual(string field, object value)
        {
            return string.Format(" {0}>={1}", field, AdaptSqlValue(value));
        }

        /// <summary>
        /// 匹配小于
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptLessThan(string field, object value)
        {
            return string.Format(" {0}<{1}", field, AdaptSqlValue(value));
        }

        /// <summary>
        /// 匹配小于等于
        /// </summary>
        /// <param name="keyField">字段名称</param>
        /// <param name="value">字段值</param>
        /// <returns></returns>
        public static string AdaptLessThanOrEqual(string field, object value)
        {
            return string.Format(" {0}<={1}", field, AdaptSqlValue(value));
        }

        /// <summary>
        /// 匹配and
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string AdaptAnd(List<string> whereList)
        {
            string[] w = new string[whereList.Count];
            int i = 0;
            foreach (string wh in whereList)
            {
                w[i] = wh;
                i++;
            }
            return String.Join(" and ", w);
        }

        /// <summary>
        /// 匹配or
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string AdaptOr(List<string> where)
        {
            string[] w = new string[where.Count];
            int i = 0;
            foreach (string wh in where)
            {
                w[i] = wh;
                i++;
            }
            return String.Join(" or ", w);
        }
        #endregion

        #region base
        /// <summary>
        /// 匹配SQL的值
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static string AdaptSqlValue(object o)
        {
            if (o is double ||
            o is float ||
            o is long ||
            o is int ||
            o is short ||
            o is byte ||
            o is decimal)
            {
                return o.ToString();
            }
            else
            {
                return string.Format("'{0}'", o);
            }
        }
        #endregion
    }
}
